package com.wanmait.cookbook.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.cookbook.vo.Cookbook;
import com.wanmait.cookbook.vo.Step;
import com.wanmait.demo.util.DBHelper;

/*
 *步骤表
 */
public class StepDAO {
	private StepDAO() {}
	private static StepDAO stepDAO = new StepDAO();
	public static StepDAO getStepDAO() {
		return stepDAO;
	}
	
	/**
	   *  根据食谱id 获得步骤
	   * @param id
	   * @return
	   */
	  public List<Step> findStepsByCookbookId(Integer id) {
	    List<Step> steps = new ArrayList<Step>();
	    Step step = null;
	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    String sql = "SELECT * FROM step WHERE cookbook_id=?";
	    try {
	      conn = DBHelper.getHelper().getConn();
	      ps = conn.prepareStatement(sql);
	      ps.setInt(1, id);
	      rs = ps.executeQuery();
	      while (rs.next()) {
	        step = new Step();
	        Cookbook cookbook = new Cookbook();
	        cookbook.setId(rs.getInt("cookbook_id"));
	        step.setId(rs.getInt("id"));
	        step.setCookbook(cookbook);
	        step.setPic(rs.getString("pic"));
	        step.setInfo(rs.getString("info"));
	        steps.add(step);
	      }
	    } catch (SQLException e) {
	      // TODO Auto-generated catch block
	      e.printStackTrace();
	    }
	    finally {
	      DBHelper.getHelper().closeConn(rs, ps, conn);
	    }
	    return steps;
	  }
	  /**
	   *  草稿箱新添加步骤
	   * @param step
	   */
	  public void addStepNew(Step step) {
	    String sql = "INSERT INTO step(cookbook_id,pic,info) values(?,?,?)";
	    DBHelper.getHelper().executeUpdate(sql, step.getCookbook().getId(),step.getPic(),step.getInfo());
	  }
	  /**
	   *  修改步骤
	   * @param step
	   */
	  public void updateStep(Step step) {
	    String sql = "UPDATE step SET pic=?,info=? WHERE id=?";
	    DBHelper.getHelper().executeUpdate(sql, step.getPic(),step.getInfo(),step.getId());
	  }
	  /**
	   *  添加 步骤
	   * @param step
	   */
	  public void addStep(Step step) {
	    String sql = "INSERT INTO step(cookbook_id,pic,info) SELECT MAX(id),?,? FROM cookbook";
	    DBHelper.getHelper().executeUpdate(sql, step.getPic(),step.getInfo());
	  }
	//ͨ�����׵�id��ò��ײ���
	public List<Step> findStepByCookbookId(int id) {
		List<Step> steps = new ArrayList<Step>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql="select * from step where cookbook_id=?";
		
		try {
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			Step step = null;
			Cookbook cookbook = null;
			while(rs.next()) {
				step = new Step();
				cookbook = new Cookbook();
				step.setId(rs.getInt("id"));
				step.setPic(rs.getString("pic"));
				step.setInfo(rs.getString("info"));
				cookbook.setId(rs.getInt("cookbook_id"));
				step.setCookbook(cookbook);
				steps.add(step);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getHelper().closeConn(rs, ps, conn);
			
		}
		return steps;
	}
}
